﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace OPMS_V1.DOL
{

    public class ListStates
    {
        private const String connString = "server=localhost;database=OPMS;Integrated Security=SSPI";
        SqlDataReader reader = null;
        SqlConnection conn = new SqlConnection(connString);


        public List<String> retrieveDistrictStates(String statename)
        {
            List<String> alldistricts = new List<string>();
            using (SqlCommand command = new SqlCommand("ListAllDistricts", conn))
            {
                try
                {
                    conn.Open();

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@statename", SqlDbType.VarChar, 50).Value = statename;

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        while (reader.Read())
                        {
                            string districtnames = reader.GetString(0);
                            if (alldistricts.Count > 0)
                            {
                                if (districtnames != "Not Available") { alldistricts.Add(" " + districtnames); }
                            }
                            else
                            {
                                alldistricts.Add(districtnames);
                            }
                        }
                    }
                    reader.Close();
                }
                catch (SqlException)
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    { conn.Close(); }
                }
            }
            return alldistricts;

        }

        public DataSet getDistrictList(string selectedState)
        {
            DataSet datafinal = new DataSet();

            using (SqlConnection conn = new SqlConnection(connString))
            {
                DataSet data = null;
                SqlDataAdapter da = null;
                SqlCommand command = null;
                command = new SqlCommand("ListAllDistricts", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@statename", SqlDbType.VarChar).Value = selectedState;
                da = new SqlDataAdapter(command);
                //Filling the DataSet with the List of Area Information
                data = new DataSet();
                da.Fill(data);

                datafinal.Merge(data, true);
            }
            return datafinal;
        }
    }
}
